using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;

using EntLibEC.Cmn;
using EntLibEC.Info.Basic;

namespace EntLibEC.Dac.Basic
{
	/// <summary>
	/// Summary description for CustomerDac.
	/// </summary>
	public class CustomerDac
	{
		public CustomerDac()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		

		public int Insert(CustomerInfo oParam)
		{
			string sql = @"INSERT INTO Ent_Customer
                            (
                            SysNo, CustomerID, Pwd, Status, 
                            CustomerName, Gender, Email, Phone, 
                            CellPhone, Fax, DwellAreaSysNo, DwellAddress, 
                            DwellZip, ReceiveName, ReceiveContact, ReceivePhone, 
                            ReceiveCellPhone, ReceiveFax, ReceiveAreaSysNo, ReceiveAddress, 
                            ReceiveZip, TotalScore, ValidScore, CardNo, 
                            Note, IsEmailConfirmed, IsSubscribe
                            )
                            VALUES (
                            @SysNo, @CustomerID, @Pwd, @Status, 
                            @CustomerName, @Gender, @Email, @Phone, 
                            @CellPhone, @Fax, @DwellAreaSysNo, @DwellAddress, 
                            @DwellZip, @ReceiveName, @ReceiveContact, @ReceivePhone, 
                            @ReceiveCellPhone, @ReceiveFax, @ReceiveAreaSysNo, @ReceiveAddress, 
                            @ReceiveZip, @TotalScore, @ValidScore, @CardNo, 
                            @Note, @IsEmailConfirmed, @IsSubscribe
                            )";

			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramCustomerID = new SqlParameter("@CustomerID", SqlDbType.NVarChar,50);
			SqlParameter paramPwd = new SqlParameter("@Pwd", SqlDbType.NVarChar,50);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
			SqlParameter paramCustomerName = new SqlParameter("@CustomerName", SqlDbType.NVarChar,50);
			SqlParameter paramGender = new SqlParameter("@Gender", SqlDbType.Int,4);
			SqlParameter paramEmail = new SqlParameter("@Email", SqlDbType.NVarChar,50);
			SqlParameter paramPhone = new SqlParameter("@Phone", SqlDbType.NVarChar,50);
			SqlParameter paramCellPhone = new SqlParameter("@CellPhone", SqlDbType.NVarChar,50);
			SqlParameter paramFax = new SqlParameter("@Fax", SqlDbType.NVarChar,50);
			SqlParameter paramDwellAreaSysNo = new SqlParameter("@DwellAreaSysNo", SqlDbType.Int,4);
			SqlParameter paramDwellAddress = new SqlParameter("@DwellAddress", SqlDbType.NVarChar,200);
			SqlParameter paramDwellZip = new SqlParameter("@DwellZip", SqlDbType.NVarChar,50);
			SqlParameter paramReceiveName = new SqlParameter("@ReceiveName", SqlDbType.NVarChar,50);
			SqlParameter paramReceiveContact = new SqlParameter("@ReceiveContact", SqlDbType.NVarChar,50);
			SqlParameter paramReceivePhone = new SqlParameter("@ReceivePhone", SqlDbType.NVarChar,50);
			SqlParameter paramReceiveCellPhone = new SqlParameter("@ReceiveCellPhone", SqlDbType.NVarChar,50);
			SqlParameter paramReceiveFax = new SqlParameter("@ReceiveFax", SqlDbType.NVarChar,50);
			SqlParameter paramReceiveAreaSysNo = new SqlParameter("@ReceiveAreaSysNo", SqlDbType.Int,4);
			SqlParameter paramReceiveAddress = new SqlParameter("@ReceiveAddress", SqlDbType.NVarChar,200);
			SqlParameter paramReceiveZip = new SqlParameter("@ReceiveZip", SqlDbType.NVarChar,50);
			SqlParameter paramTotalScore = new SqlParameter("@TotalScore", SqlDbType.Int,4);
			SqlParameter paramValidScore = new SqlParameter("@ValidScore", SqlDbType.Int,4);
			SqlParameter paramCardNo = new SqlParameter("@CardNo", SqlDbType.NVarChar,50);
			SqlParameter paramNote = new SqlParameter("@Note", SqlDbType.NVarChar,500);
			SqlParameter paramIsEmailConfirmed = new SqlParameter("@IsEmailConfirmed", SqlDbType.Int,4);
			SqlParameter paramIsSubscribe = new SqlParameter("@IsSubscribe", SqlDbType.Int,4);

			if ( oParam.SysNo != AppConst.IntNull)
				paramSysNo.Value = oParam.SysNo;
			else
				paramSysNo.Value = System.DBNull.Value;
			if ( oParam.CustomerID != AppConst.StringNull)
				paramCustomerID.Value = oParam.CustomerID;
			else
				paramCustomerID.Value = System.DBNull.Value;
			if ( oParam.Pwd != AppConst.StringNull)
				paramPwd.Value = oParam.Pwd;
			else
				paramPwd.Value = System.DBNull.Value;
			if ( oParam.Status != AppConst.IntNull)
				paramStatus.Value = oParam.Status;
			else
				paramStatus.Value = System.DBNull.Value;
			if ( oParam.CustomerName != AppConst.StringNull)
				paramCustomerName.Value = oParam.CustomerName;
			else
				paramCustomerName.Value = System.DBNull.Value;
			if ( oParam.Gender != AppConst.IntNull)
				paramGender.Value = oParam.Gender;
			else
				paramGender.Value = System.DBNull.Value;
			if ( oParam.Email != AppConst.StringNull)
				paramEmail.Value = oParam.Email;
			else
				paramEmail.Value = System.DBNull.Value;
			if ( oParam.Phone != AppConst.StringNull)
				paramPhone.Value = oParam.Phone;
			else
				paramPhone.Value = System.DBNull.Value;
			if ( oParam.CellPhone != AppConst.StringNull)
				paramCellPhone.Value = oParam.CellPhone;
			else
				paramCellPhone.Value = System.DBNull.Value;
			if ( oParam.Fax != AppConst.StringNull)
				paramFax.Value = oParam.Fax;
			else
				paramFax.Value = System.DBNull.Value;
			
			paramDwellAreaSysNo.Value = oParam.DwellAreaSysNo;
			
			if ( oParam.DwellAddress != AppConst.StringNull)
				paramDwellAddress.Value = oParam.DwellAddress;
			else
				paramDwellAddress.Value = System.DBNull.Value;
			if ( oParam.DwellZip != AppConst.StringNull)
				paramDwellZip.Value = oParam.DwellZip;
			else
				paramDwellZip.Value = System.DBNull.Value;
			if ( oParam.ReceiveName != AppConst.StringNull)
				paramReceiveName.Value = oParam.ReceiveName;
			else
				paramReceiveName.Value = System.DBNull.Value;
			if ( oParam.ReceiveContact != AppConst.StringNull)
				paramReceiveContact.Value = oParam.ReceiveContact;
			else
				paramReceiveContact.Value = System.DBNull.Value;
			if ( oParam.ReceivePhone != AppConst.StringNull)
				paramReceivePhone.Value = oParam.ReceivePhone;
			else
				paramReceivePhone.Value = System.DBNull.Value;
			if ( oParam.ReceiveCellPhone != AppConst.StringNull)
				paramReceiveCellPhone.Value = oParam.ReceiveCellPhone;
			else
				paramReceiveCellPhone.Value = System.DBNull.Value;
			if ( oParam.ReceiveFax != AppConst.StringNull)
				paramReceiveFax.Value = oParam.ReceiveFax;
			else
				paramReceiveFax.Value = System.DBNull.Value;
			
			paramReceiveAreaSysNo.Value = oParam.ReceiveAreaSysNo;
			
			if ( oParam.ReceiveAddress != AppConst.StringNull)
				paramReceiveAddress.Value = oParam.ReceiveAddress;
			else
				paramReceiveAddress.Value = System.DBNull.Value;
			if ( oParam.ReceiveZip != AppConst.StringNull)
				paramReceiveZip.Value = oParam.ReceiveZip;
			else
				paramReceiveZip.Value = System.DBNull.Value;
			if ( oParam.TotalScore != AppConst.IntNull)
				paramTotalScore.Value = oParam.TotalScore;
			else
				paramTotalScore.Value = System.DBNull.Value;
			if ( oParam.ValidScore != AppConst.IntNull)
				paramValidScore.Value = oParam.ValidScore;
			else
				paramValidScore.Value = System.DBNull.Value;
			if ( oParam.CardNo != AppConst.StringNull)
				paramCardNo.Value = oParam.CardNo;
			else
				paramCardNo.Value = System.DBNull.Value;
			if ( oParam.Note != AppConst.StringNull)
				paramNote.Value = oParam.Note;
			else
				paramNote.Value = System.DBNull.Value;
			if ( oParam.IsEmailConfirmed != AppConst.IntNull)
				paramIsEmailConfirmed.Value = oParam.IsEmailConfirmed;
			else
				paramIsEmailConfirmed.Value = System.DBNull.Value;

			if ( oParam.IsSubscribe != AppConst.IntNull)
				paramIsSubscribe.Value = oParam.IsSubscribe;
			else
				paramIsSubscribe.Value = System.DBNull.Value;


			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramCustomerID);
			cmd.Parameters.Add(paramPwd);
			cmd.Parameters.Add(paramStatus);
			cmd.Parameters.Add(paramCustomerName);
			cmd.Parameters.Add(paramGender);
			cmd.Parameters.Add(paramEmail);
			cmd.Parameters.Add(paramPhone);
			cmd.Parameters.Add(paramCellPhone);
			cmd.Parameters.Add(paramFax);
			cmd.Parameters.Add(paramDwellAreaSysNo);
			cmd.Parameters.Add(paramDwellAddress);
			cmd.Parameters.Add(paramDwellZip);
			cmd.Parameters.Add(paramReceiveName);
			cmd.Parameters.Add(paramReceiveContact);
			cmd.Parameters.Add(paramReceivePhone);
			cmd.Parameters.Add(paramReceiveCellPhone);
			cmd.Parameters.Add(paramReceiveFax);
			cmd.Parameters.Add(paramReceiveAreaSysNo);
			cmd.Parameters.Add(paramReceiveAddress);
			cmd.Parameters.Add(paramReceiveZip);
			cmd.Parameters.Add(paramTotalScore);
			cmd.Parameters.Add(paramValidScore);
			cmd.Parameters.Add(paramCardNo);
			cmd.Parameters.Add(paramNote);
			cmd.Parameters.Add(paramIsEmailConfirmed);
			cmd.Parameters.Add(paramIsSubscribe);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
		
		public int Update(Hashtable paramHash)
		{
			StringBuilder sb = new StringBuilder(200);
			sb.Append("UPDATE Ent_Customer SET ");

			if ( paramHash != null && paramHash.Count != 0 )
			{
				int index = 0;
				foreach(string key in paramHash.Keys)
				{
					object item = paramHash[key];
					if ( key.ToLower()=="sysno" )
						continue;

					if ( index != 0 )
						sb.Append(",");
					index++;

					
					if (item is int || item is decimal)
					{
						sb.Append(key).Append("=").Append(item.ToString());
					}
					else if (item is string)
					{
						sb.Append(key).Append(" = ").Append(Util.ToSqlString(item.ToString()));
					}
					else if (item is DateTime)
					{
						sb.Append(key).Append(" = cast(").Append( Util.ToSqlString(((DateTime)item).ToString(AppConst.DateFormatLong))).Append(" as DateTime)");
					}
				}
			}

			sb.Append(" WHERE SysNo=").Append(paramHash["SysNo"]);

			return SqlHelper.ExecuteNonQuery(sb.ToString());
		}		
	}
}
